﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Diagnostics;
using System.IO;
using System.Data.OleDb;
using System.Data.OracleClient;
using WHC.QQCollector.Commons;

namespace ImportExcelToPark
{
    public partial class FrmImportExcelData : Form
    {
        private AppConfig config = new AppConfig();
        private DataSet myDs = new DataSet();
        private string connectionStringFormat = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '{0}';Extended Properties=Excel 8.0";

        public FrmImportExcelData()
        {
            InitializeComponent();
        }

        private void lnkExcel_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            try
            {
                const string helpfile = "资料导入模板.xls";
                Process.Start(helpfile);
            }
            catch (Exception)
            {
                MessageUtil.ShowWarning("文件打开失败");
            }
        }

        private void FrmImportExcelData_Load(object sender, EventArgs e)
        {
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();

            openFileDialog1.InitialDirectory = "F:\\工作文档";
            openFileDialog1.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";
            openFileDialog1.FilterIndex = 2;
            openFileDialog1.RestoreDirectory = true;

            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                this.txtFilePath.Text = openFileDialog1.FileName;
                comboBox1.DisplayMember = "Table_Name";
                comboBox1.ValueMember = "Table_Name";
                comboBox1.DataSource = GetExcelTableNames(txtFilePath.Text);
            }
        }

        private void btnViewData_Click(object sender, EventArgs e)
        {
            if (this.txtFilePath.Text == "")
            {
                MessageUtil.ShowTips("请选择指定的Excel文件");
                return;
            }

            string connectString = string.Format(connectionStringFormat, this.txtFilePath.Text);
                        
            try
            {
                myDs.Tables.Clear();
                myDs.Clear();
                OleDbConnection cnnxls = new OleDbConnection(connectString);
                string mystr = string.Format("select * from [{0}]", comboBox1.SelectedValue);
                OleDbDataAdapter myDa = new OleDbDataAdapter(mystr, cnnxls);
                myDa.Fill(myDs, "c");

                dataGrid1.DataSource = myDs.Tables[0];
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnSaveData_Click(object sender, EventArgs e)
        {
            if (this.txtFilePath.Text == "")
            {
                MessageUtil.ShowTips("请选择指定的Excel文件");
                return;
            }

            if (MessageUtil.ShowYesNoAndWarning("该操作将把数据导入到系统的用户数据库中，您确定是否继续？") == DialogResult.Yes)
            {
                InsertData();
            }
        }

        private bool CheckIsDate(string columnName)
        {
            string str = ",PREPARE_DATE,COPY_DATE,COPY_VALIDITY,BUSINESS_VALIDITY,OPENING_APPROVAL_DATE,OPENING_DATE,EDITTIME,LICENSE_DATE,LICENSE_VALIDITY,TEMP_OPENING_DATE,LICENSE_START_DATE,ADDTIME,EDITTIME,";
            return str.Contains("," + columnName.ToUpper() + ",");
        }

        private bool CheckIsNumeric(string columnName)
        {
            string str = ",FIXED_CAPITAL,REG_CAPITAL,MARGIN,PARK_AREA,PARK_SPACE_NUMBER,";
            return str.Contains("," + columnName.ToUpper() + ",");
        }

        private DataTable GetExcelTableNames(string filename)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            OleDbConnection cnnxls = new OleDbConnection();
            try
            {
                string mystring = "Provider = Microsoft.Jet.OLEDB.4.0 ;Extended Properties=Excel 8.0;Data Source =" + filename;
                cnnxls.ConnectionString = mystring;
                cnnxls.Open();
                dt = cnnxls.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            }
            finally
            {
                cnnxls.Close();
            }
            return dt;
        }

        private void InsertData()
        {
            int intOk = 0;
            int intFail = 0;

            if (myDs != null && myDs.Tables[0].Rows.Count > 0)
            {
                string accessConnectString = config.GetConnectionString("DataAccess");
                OracleConnection conn = new OracleConnection(accessConnectString);
                conn.Open();
                OracleCommand com = null;

                #region 组装字段列表
                string insertColumnString = "ID,";
                DataTable dt = myDs.Tables[0];
                int k = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    insertColumnString += string.Format("{0},", col.ColumnName);
                }
                insertColumnString = insertColumnString.Trim(',');

                #endregion

                try
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (dr[0].ToString() == "")
                        {
                            continue;
                        }

                        #region 组装Sql语句
                        string insertValueString = "SEQ_TBPARK_ENTERPRISE.Nextval,";
                        string updateValueString = "";
                        string COMPANY_CODE = dr["COMPANY_CODE"].ToString().Replace("<空>", "");

                        #region 拼接Sql字符串

                        for(int i = 0; i < dt.Columns.Count; i++)
                        {
                            string originalValue = dr[i].ToString().Replace("<空>", "");
                            //if (!CheckIsDate(dt.Rows[0][i].ToString()))
                            if (!CheckIsDate(dt.Columns[i].ColumnName))
                            {
                                if (!string.IsNullOrEmpty(originalValue))
                                {
                                    if (CheckIsNumeric(dt.Columns[i].ColumnName))
                                    {
                                        insertValueString += string.Format("'{0}',", Convert.ToDecimal(originalValue));
                                        updateValueString += string.Format("{0}='{1}',", dt.Columns[i].ColumnName, Convert.ToDecimal(originalValue));
                                    }
                                    else
                                    {
                                        insertValueString += string.Format("'{0}',", originalValue);
                                        updateValueString += string.Format("{0}='{1}',", dt.Columns[i].ColumnName, originalValue);
                                    }
                                }
                                else
                                {
                                    insertValueString += string.Format("NULL,");
                                    updateValueString += string.Format("{0}=NULL,", dt.Columns[i].ColumnName);
                                }
                            }
                            else
                            {
                                if (!string.IsNullOrEmpty(originalValue))
                                {
                                    insertValueString += string.Format("to_date('{0}','yyyy-mm-dd'),", Convert.ToDateTime(originalValue).ToString("yyyy-MM-dd"));
                                    updateValueString += string.Format("{0}=to_date('{1}','yyyy-mm-dd'),", dt.Columns[i].ColumnName, Convert.ToDateTime(originalValue).ToString("yyyy-MM-dd"));
                                }
                                else
                                {
                                    insertValueString += string.Format("NULL,");
                                    updateValueString += string.Format("{0}=NULL,", dt.Columns[i].ColumnName);
                                }
                            }
                        }
                        insertValueString = insertValueString.Trim(',');
                        updateValueString = updateValueString.Trim(','); 
                        #endregion

                        string insertSql = string.Format(@"INSERT INTO tbpark_enterprise ({0}) VALUES({1})", insertColumnString, insertValueString);
                        string updateSql = string.Format("Update tbpark_enterprise set {0} Where COMPANY_CODE='{1}' ", updateValueString, COMPANY_CODE);
                        string checkExistSql = string.Format("Select count(*) from tbpark_enterprise where COMPANY_CODE='{0}' ", COMPANY_CODE);
                        #endregion

                        #region 写入数据
                        try
                        {
                            com = new OracleCommand();
                            com.Connection = conn;
                            com.CommandText = checkExistSql;
                            object objCount = com.ExecuteScalar();

                            bool succeed = false;
                            bool exist = Convert.ToInt32(objCount) > 0;
                            if (exist)
                            {
                                //需要更新
                                //WriteString(updateSql);
                                com.CommandText = updateSql;
                                succeed = com.ExecuteNonQuery() > 0;
                            }
                            else
                            {
                                //需要插入
                                //WriteString2(insertSql);
                                com.CommandText = insertSql;
                                succeed = com.ExecuteNonQuery() > 0;
                            }

                            if (succeed)
                            {
                                intOk++;
                            }
                            else
                            {
                                intFail++;
                            }
                        }
                        catch (Exception ex)
                        {
                            intFail++;
                            WriteString(com.CommandText);
                            LogHelper.Error(ex);
                            break;
                        }

                        #endregion
                    }

                    #region 关闭
                    if (conn != null && conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                    if (com != null)
                    {
                        com.Dispose();
                    }
                    #endregion
                }
                catch (Exception ex)
                {
                    LogHelper.Error(ex);
                    MessageUtil.ShowError(ex.ToString());
                }

                if (intOk > 0 || intFail > 0)
                {
                    string tips = string.Format("数据导入成功：{0}个，失败：{1}个", intOk, intFail);
                    MessageUtil.ShowTips(tips);
                }
            }
        }

        private void WriteString(string str)
        {
            string fileName = "sql.txt";
            using (FileStream fs = new FileStream(fileName, FileMode.Append, FileAccess.Write))
            {
                using (StreamWriter tw = new StreamWriter(fs))
                {
                    tw.WriteLine(str);
                }
            }
        }
        private void WriteString2(string str)
        {
            string fileName = "sql2.txt";
            using (FileStream fs = new FileStream(fileName, FileMode.Append, FileAccess.Write))
            {
                using (StreamWriter tw = new StreamWriter(fs))
                {
                    tw.WriteLine(str);
                }
            }
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        #region 辅助函数

        public bool IsChinese(string str_chinese)
        {
            return System.Text.RegularExpressions.Regex.IsMatch(str_chinese, @"[\u4e00-\u9fa5]");
        }

        /// <summary>
        /// 根据nameList里面的字段创建一个表格,返回该表格的DataSet
        /// </summary>
        /// <param name="nameList">包含字段信息的列表</param>
        /// <returns>DataSet</returns>
        public DataTable CreateTable(List<string> nameList)
        {
            string tempName = DateTime.Now.Millisecond.ToString().Substring(0, 5);
            return CreateTable(nameList, tempName);
        }

        /// <summary>
        /// 根据nameList里面的字段创建一个表格,返回该表格的DataSet
        /// </summary>
        /// <param name="nameList">包含字段信息的列表</param>
        /// <returns>DataSet</returns>
        public DataTable CreateTable(List<string> nameList, string tableName)
        {
            if (nameList.Count <= 0)
                return null;

            DataTable myDataTable = new DataTable(tableName);
            DataColumn myDataColumn;

            foreach (string columnName in nameList)
            {
                myDataColumn = new DataColumn();
                myDataColumn.DataType = Type.GetType("System.String");
                myDataColumn.ColumnName = columnName;
                myDataTable.Columns.Add(myDataColumn);
            }
            return myDataTable;
        }

        public DataTable CreateTable(string nameString)
        {
            string[] nameArray = nameString.Split(new char[] { ',', ';' });
            List<string> nameList = new List<string>();
            foreach (string item in nameArray)
            {
                if (!string.IsNullOrEmpty(item))
                {
                    nameList.Add(item);
                }
            }

            return CreateTable(nameList);
        } 
        #endregion

        private void dataGrid1_Navigate(object sender, NavigateEventArgs ne)
        {

        }
    }
}
